package algorithm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * 用于计算平均值和方差，适用于每次作业的班级统计
 */
public class DescribeData {
	private int n;// 第几次作业
	private Connection connection;
	private String url;
	private String username;
	private String password;

	public DescribeData(int n) throws SQLException {
		this.n = n;

		String url = "";
		String username = "";
		String password = "";

	}

	// 计算平均值，还要供计算标准差后续使用
	public double computeAver() throws SQLException {
		Connection connection = DriverManager.getConnection(url, username, password);
		try {
			String averStat = "select avg(score) from grade where id = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(averStat);
			preparedStatement.setInt(1, n);
			ResultSet resultSet = preparedStatement.executeQuery();
			double res = 0.0;
			while (resultSet.next()) {
				res = resultSet.getDouble(1);
			}
			preparedStatement.close();
			return res;
		} finally {
			// TODO: handle finally clause
			connection.close();
		}

	}

	/*
	 * 计算标准差 这边为简便起见，并没有使用统计学中常用的 除以n - 1来消除自由度
	 */
	public double computeStandardDeviation() throws SQLException {
		Connection connection = DriverManager.getConnection(url, username, password);
		try {
			String stdevStat = "select std(score) from grade where id = ?";
			PreparedStatement preparedStatement = connection.prepareStatement(stdevStat);
			preparedStatement.setInt(1, n);
			ResultSet resultSet = preparedStatement.executeQuery();
			double res = 0.0;
			while (resultSet.next()) {
				res = resultSet.getDouble(1);
			}
			preparedStatement.close();
			return res;
		} finally {
			// TODO: handle finally clause
			connection.close();
			
		}
	}
}
